{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# CRUD using SQLAlchemy ORM\r\n",
    "# 使用 SQLAlchemy ORM 的 CRUD 操作"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "source": [
    "from IPython.core.interactiveshell import InteractiveShell\r\n",
    "InteractiveShell.ast_node_interactivity = \"all\""
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "source": [
    "from sqlalchemy import create_engine, MetaData, Table, Integer, String, \\\r\n",
    "    Column, DateTime, ForeignKey, Numeric, SmallInteger, CheckConstraint\r\n",
    "\r\n",
    "from sqlalchemy.ext.declarative import declarative_base\r\n",
    "from sqlalchemy.orm import relationship\r\n",
    "\r\n",
    "from datetime import datetime\r\n",
    "\r\n",
    "# <!-- engine = create_engine(\"postgres+psycopg2://postgres:pass@localhost/sqlalchemy_tuts\") -->\r\n",
    "engine = create_engine(\"mysql+pymysql://root:flask123@localhost/sqlalchemy_tuts\")\r\n",
    "\r\n",
    "Base = declarative_base()\r\n",
    "\r\n",
    "class Customer(Base):\r\n",
    "    __tablename__ = 'customers'\r\n",
    "    id = Column(Integer(), primary_key=True)\r\n",
    "    first_name = Column(String(100), nullable=False)\r\n",
    "    last_name = Column(String(100), nullable=False)\r\n",
    "    username = Column(String(50), nullable=False)\r\n",
    "    email = Column(String(200), nullable=False)\r\n",
    "    address = Column(String(200), nullable=False)\r\n",
    "    town = Column(String(50), nullable=False)\r\n",
    "    created_on = Column(DateTime(), default=datetime.now)\r\n",
    "    updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)\r\n",
    "    orders = relationship(\"Order\", backref='customer')\r\n",
    "    \r\n",
    "    def __repr__(self):\r\n",
    "        return \"<Customer:{0}-{1}>\".format(self.id, self.username)\r\n",
    "\r\n",
    "\r\n",
    "class Item(Base):\r\n",
    "    __tablename__ = 'items'\r\n",
    "    id = Column(Integer(), primary_key=True)\r\n",
    "    name = Column(String(200), nullable=False)\r\n",
    "    cost_price =  Column(Numeric(10, 2), nullable=False)\r\n",
    "    selling_price = Column(Numeric(10, 2), nullable=False)\r\n",
    "    quantity = Column(SmallInteger(), nullable=False)\r\n",
    "#     orders = relationship(\"Order\", backref='customer')\r\n",
    "\r\n",
    "    def __repr__(self):\r\n",
    "        return \"<Item:{0}-{1}>\".format(self.id, self.name)    \r\n",
    "    \r\n",
    "    __table_args__ = (\r\n",
    "        CheckConstraint('quantity > 0', name='quantity_check'),\r\n",
    "    )\r\n",
    "    \r\n",
    "\r\n",
    "class Order(Base):\r\n",
    "    __tablename__ = 'orders'\r\n",
    "    id = Column(Integer(), primary_key=True)\r\n",
    "    customer_id = Column(Integer(), ForeignKey('customers.id'))\r\n",
    "    date_placed = Column(DateTime(), default=datetime.now, nullable=False)\r\n",
    "    date_shipped = Column(DateTime())\r\n",
    "#     items = relationship(\"OrderLine\")\r\n",
    "    \r\n",
    "    def __repr__(self):\r\n",
    "        return \"<Order:{0}>\".format(self.id)\r\n",
    "    \r\n",
    "\r\n",
    "class OrderLine(Base):\r\n",
    "    __tablename__ = 'order_lines'\r\n",
    "    id =  Column(Integer(), primary_key=True)\r\n",
    "    order_id = Column(Integer(), ForeignKey('orders.id'))\r\n",
    "    item_id = Column(Integer(), ForeignKey('items.id'))\r\n",
    "    quantity = Column(SmallInteger())\r\n",
    "    order = relationship(\"Order\", backref='order_lines')\r\n",
    "    item = relationship(\"Item\")\r\n",
    "    \r\n",
    "    def __repr__(self):\r\n",
    "        return \"<OrderLine:{0}>\".format(self.id)    \r\n",
    "\r\n",
    "Base.metadata.drop_all(engine)\r\n",
    "Base.metadata.create_all(engine)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Creating Session 创建会话\r\n",
    "When using SQLAlchemy ORM, we interact with the database using the `Session` object. The `Session` object also wraps the database connection and transaction. The transaction implicitly starts as soon as the `Session` starts communicating with the database and will remain open until the `Session` is committed, rolled back or closed.\r\n",
    "\r\n",
    "在使用 SQLAlchemy ORM 时，我们使用 `Session` 对象与数据库进行交互。 `Session` 对象还包装了数据库连接和事务。一旦 `Session` 开始与数据库通信，事务就会隐式启动，并将保持打开状态，直到 `Session` 被提交、回滚或关闭。\r\n",
    "\r\n",
    "One way to create a `Session` object is to use the `Session` class from the `sqlalchemy.orm` package.\r\n",
    "\r\n",
    "创建 `Session` 对象的一种方法是使用 `sqlalchemy.orm` 包中的 `Session` 类。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "# 不用运行\r\n",
    "# from sqlalchemy import create_engine\r\n",
    "# from sqlalchemy.orm import Session\r\n",
    "# engine = create_engine(\"mysql+pymysql://root:flask123@localhost/sqlalchemy_tuts\")\r\n",
    "# session = Session(bind=engine)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "You will have to create the `Session` object everytime you want to communicate with the database.\r\n",
    "\r\n",
    "每次想要与数据库通信时，您都必须创建 `Session` 对象。\r\n",
    "\r\n",
    "The `Session` constructor accepts a number of argument to customize its working. If we choose to create Session using this method, we would have to call the `Session` constructor with the same set of parameters over and over again throughout the application.\r\n",
    "\r\n",
    "`Session` 构造函数接受许多参数来自定义它的工作。如果我们选择使用这个方法创建 `Session` ，我们将不得不在整个应用程序中使用相同的参数集一遍又一遍地调用 `Session` 构造函数。\r\n",
    "\r\n",
    "To make things easier, SQLAlchemy provides `sessionmaker` class which creates `Session` class with default arguments set for its constructor.\r\n",
    "\r\n",
    "为了让事情变得更简单，SQLAlchemy 提供 `sessionmaker` 类，它创建 `Session` 类，并为其构造函数设置默认参数。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "source": [
    "from sqlalchemy.orm import sessionmaker, Session\r\n",
    "Session = sessionmaker(bind=engine)\r\n",
    "session = Session()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "You should call call `sessionmaker` once in your application at the global scope.\r\n",
    "\r\n",
    "在应用程序中，您应该在全局范围内调用 `sessionmaker` 一次。\r\n",
    "\r\n",
    "Once we have access to the `custom` Session class you can instantiate it as many time as you need without passing any arguments to it.\r\n",
    "\r\n",
    "一旦我们可以访问自定义 `Session` 类，您就可以根据需要实例化它，而不需要向它传递任何参数。\r\n",
    "\r\n",
    "`session = Session()`\r\n",
    "\r\n",
    "Note that instantiating `Session` object doesn't instantly establish connection to the database. The connection is established only when you start sending queries to the database.\r\n",
    "\r\n",
    "注意，实例化 `Session` 对象并不能立即建立到数据库的连接。只有当您开始向数据库发送查询时才建立连接。\r\n",
    "\r\n",
    "Note: There is more to Session than this tutorial covers. For more details checkout the official documentation.\r\n",
    "\r\n",
    "注意: 除了本教程所涵盖的内容之外，还有更多关于 `Session` 的内容, 请查看官方文档。\r\n",
    "\r\n",
    "`官方文档` https://docs.sqlalchemy.org/en/13/orm/session.html?highlight=session#module-sqlalchemy.orm.session"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "\r\n",
    "# Inserting Data 插入数据\r\n",
    "To create a new record using SQLAlchemy ORM, we follow these steps:\r\n",
    "要使用 SQLAlchemy ORM 创建一个新记录，我们按照以下步骤操作:\r\n",
    "\r\n",
    "1. Create an object. 创建一个对象\r\n",
    "\r\n",
    "2. Add the object to the session. 将对象添加到会话中\r\n",
    "\r\n",
    "3. Commit the session. 提交会议\r\n",
    "\r\n",
    "Let's create two new `Customer` objects as follows:\r\n",
    "让我们创建两个新的 `Customer` 对象，如下所示:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "source": [
    "c1 = Customer(first_name = 'Toby', \r\n",
    "            last_name = 'Miller', \r\n",
    "            username = 'tmiller', \r\n",
    "            email = 'tmiller@example.com', \r\n",
    "            address = '1662 Kinney Street',\r\n",
    "            town = 'Wolfden'\r\n",
    "            )\r\n",
    "\r\n",
    "c2 = Customer(first_name = 'Scott', \r\n",
    "            last_name = 'Harvey', \r\n",
    "            username = 'scottharvey', \r\n",
    "            email = 'scottharvey@example.com', \r\n",
    "            address = '424 Patterson Street',\r\n",
    "            town = 'Beckinsdale'\r\n",
    "            )\r\n",
    "c1, c2"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(<Customer:None-tmiller>, <Customer:None-scottharvey>)"
      ]
     },
     "metadata": {},
     "execution_count": 4
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Here we have created two Customer objects. We can access the attributes of an object using the dot(.) operator as follows:\r\n",
    "\r\n",
    "在这里，我们创建了两个 Customer 对象。我们可以使用点(.)访问对象的属性操作符如下:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "source": [
    "c1.first_name, c1.last_name\r\n",
    "c2.first_name, c2.last_name"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "('Toby', 'Miller')"
      ]
     },
     "metadata": {},
     "execution_count": 5
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "('Scott', 'Harvey')"
      ]
     },
     "metadata": {},
     "execution_count": 5
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Next, we add the objects to the session.\r\n",
    "\r\n",
    "接下来，我们将对象添加到会话中。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "source": [
    "session.add(c1)\r\n",
    "session.add(c2)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Adding objects to the session doesn't actually writes them to the database, it only prepares the objects to be saved in the next commit. We can verify this by checking the primary key of the objects.\r\n",
    "\r\n",
    "向会话中添加对象实际上并不会将它们写入数据库，它只准备在下一次提交中保存的对象。我们可以通过检查对象的主键来验证这一点。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "source": [
    "c1.id, c2.id"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(None, None)"
      ]
     },
     "metadata": {},
     "execution_count": 8
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "The value of id attribute of both the objects is None. That means our objects are not yet saved in the database.\r\n",
    "这两个对象的 id 属性值都是 None。这意味着我们的对象还没有保存在数据库中。\r\n",
    "\r\n",
    "Instead of adding one object to the session at a time, we can use `add_all()` method. The `add_all()` method accepts a list of objects to be added to the session.\r\n",
    "\r\n",
    "我们可以使用 `add_all()`方法，而不是一次向会话中添加一个对象。`add_all()` 方法接受一组要添加到会话的对象(注意类型为数组list)。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "source": [
    "session.add_all([c1, c2])"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Adding an object to the session multiple times doesn't throw any errors. At any time, you can view the objects added to the session using session.new.\r\n",
    "\r\n",
    "多次向会话中添加对象不会引发任何错误。在任何时候，您都可以使用 session.new 查看添加到会话中的对象。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "source": [
    "session.new"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "IdentitySet([<Customer:None-tmiller>, <Customer:None-scottharvey>])"
      ]
     },
     "metadata": {},
     "execution_count": 9
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Finally, to save the objects to the database call commit() method as follows:\r\n",
    "\r\n",
    "最后，将对象保存到数据库调用 commit ()方法，如下所示:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "source": [
    "session.commit()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Once you commit the transaction, the connection resources referenced by the Session object is returned to the connection pool. Subsequent operations will occur in a new transaction.\r\n",
    "\r\n",
    "提交事务后， `Session` 对象引用的连接资源将返回到连接池。后续操作将在新事务中发生。\r\n",
    "\r\n",
    "Accessing the `id` attribute of the `Customer` object will now return the primary key instead of `None`.\r\n",
    "\r\n",
    "访问 `Customer` 对象的 `id` 属性现在将返回主键，而不是 `None` 。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "source": [
    "c1.id, c2.id"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(1, 2)"
      ]
     },
     "metadata": {},
     "execution_count": 11
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "At this point, the Customer table should look like this:\r\n",
    "此时，Customer 表应该如下所示:\r\n",
    "\r\n",
    "![img](Selection_005-ba3cff10-46c5-4818-9456-5863e3442589.png)\r\n",
    "\r\n",
    "Our customers haven't ordered anything. So c1.orders and c2.orders would return an empty list.\r\n",
    "\r\n",
    "我们的客户没有订购任何东西，所以 c1.orders 和 c2.orders 将返回一个空列表。\r\n"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "source": [
    "c1.orders, c2.orders"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "([], [])"
      ]
     },
     "metadata": {},
     "execution_count": 12
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Let's add some more customers to the customers table:\r\n",
    "\r\n",
    "让我们在客户表中添加更多的客户:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "source": [
    "c3 = Customer(\r\n",
    "            first_name = \"John\", \r\n",
    "            last_name = \"Lara\", \r\n",
    "            username = \"johnlara\", \r\n",
    "            email = \"johnlara@mail.com\", \r\n",
    "            address = \"3073 Derek Drive\",\r\n",
    "            town = \"Norfolk\"\r\n",
    ")\r\n",
    "\r\n",
    "c4 = Customer(          \r\n",
    "            first_name = \"Sarah\", \r\n",
    "            last_name = \"Tomlin\", \r\n",
    "            username = \"sarahtomlin\", \r\n",
    "            email = \"sarahtomlin@mail.com\",\r\n",
    "            address = \"3572 Poplar Avenue\",\r\n",
    "            town = \"Norfolk\"        \r\n",
    ")\r\n",
    "\r\n",
    "c5 = Customer(first_name = 'Toby', \r\n",
    "            last_name = 'Miller', \r\n",
    "            username = 'tmiller', \r\n",
    "            email = 'tmiller@example.com', \r\n",
    "            address = '1662 Kinney Street',\r\n",
    "            town = 'Wolfden'\r\n",
    "            )\r\n",
    "\r\n",
    "c6 = Customer(first_name = 'Scott', \r\n",
    "            last_name = 'Harvey', \r\n",
    "            username = 'scottharvey', \r\n",
    "            email = 'scottharvey@example.com', \r\n",
    "            address = '424 Patterson Street',\r\n",
    "            town = 'Beckinsdale'\r\n",
    "            )\r\n",
    "\r\n",
    "session.add_all([c3, c4, c5, c6])\r\n",
    "session.commit()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Before we can take orders, let's add some products to the items table.\r\n",
    "\r\n",
    "在接受订单之前，我们先将一些产品添加到项目表中。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "source": [
    "i1 = Item(name = 'Chair', cost_price = 9.21, selling_price = 10.81, quantity = 5)\r\n",
    "i2 = Item(name = 'Pen', cost_price = 3.45, selling_price = 4.51, quantity = 3)\r\n",
    "i3 = Item(name = 'Headphone', cost_price = 15.52, selling_price = 16.81, quantity = 50)\r\n",
    "i4 = Item(name = 'Travel Bag', cost_price = 20.1, selling_price = 24.21, quantity = 50)\r\n",
    "i5 = Item(name = 'Keyboard', cost_price = 20.1, selling_price = 22.11, quantity = 50)\r\n",
    "i6 = Item(name = 'Monitor', cost_price = 200.14, selling_price = 212.89, quantity = 50)\r\n",
    "i7 = Item(name = 'Watch', cost_price = 100.58, selling_price = 104.41, quantity = 50)\r\n",
    "i8 = Item(name = 'Water Bottle', cost_price = 20.89, selling_price = 25, quantity = 50)\r\n",
    "\r\n",
    "session.add_all([i1, i2, i3, i4, i5, i6, i7, i8])\r\n",
    "session.commit()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Create some orders now:\r\n",
    "\r\n",
    "现在创建一些订单:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "source": [
    "o1 = Order(customer = c1)\r\n",
    "o2 = Order(customer = c1)\r\n",
    "\r\n",
    "order_line1 = OrderLine(order = o1, item = i1, quantity =  3)\r\n",
    "order_line2 = OrderLine(order = o1, item = i2, quantity =  2)\r\n",
    "order_line3 = OrderLine(order = o2, item = i1, quantity =  1)\r\n",
    "order_line4 = OrderLine(order = o2, item = i2, quantity =  4)\r\n",
    "\r\n",
    "session.add_all([o1, o2])\r\n",
    "\r\n",
    "session.new\r\n",
    "session.commit()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "IdentitySet([<Order:None>, <Order:None>, <OrderLine:None>, <OrderLine:None>, <OrderLine:None>, <OrderLine:None>])"
      ]
     },
     "metadata": {},
     "execution_count": 15
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Notice that we are only adding `Order` objects (i.e `o1` and `o2`) to the session. The `Order` and `OrderLine` object are associated with a one-to-many relationship. Adding an `Order` object to the session implicitly adds related `OrderLine` object to the session as well. However, even you still add the `OrderLine` object manually to the session, you will not get any error.\r\n",
    "\r\n",
    "注意，我们只向会话中添加 `Order` 对象(即 `o1` 和 `o2` )。`Order` 和 `OrderLine` 对象与一对多关系相关联。向会话添加一个 `Order` 对象也会隐式地向会话添加相关的 `OrderLine` 对象。但是，即使您仍然手动将 `OrderLine` 对象添加到会话中，您也不会得到任何错误。\r\n",
    "\r\n",
    "Instead of passing Order object at the time of creating OrderLine instance, we can also do this:\r\n",
    "\r\n",
    "我们不需要在创建 `OrderLine` 实例时传递 `Order` 对象，我们也可以这样做:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "source": [
    "o3 = Order(customer = c1)\r\n",
    "\r\n",
    "orderline1 = OrderLine(item = i1, quantity = 5)\r\n",
    "orderline2 = OrderLine(item = i2, quantity = 10)\r\n",
    "\r\n",
    "o3.order_lines.append(orderline1)\r\n",
    "o3.order_lines.append(orderline2)\r\n",
    "\r\n",
    "session.add_all([o3])\r\n",
    "\r\n",
    "session.commit()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "After this commit, the `orders` and `order_lines` table should now look like this:\r\n",
    "\r\n",
    "在这个提交之后，`order` 和 `order_lines` 表现在应该如下所示:\r\n",
    "\r\n",
    "[]\r\n",
    "\r\n",
    "Access the `orders` attribute of the `Customer` object again, this time you will get a non-empty list like this:\r\n",
    "\r\n",
    "再次访问 `Customer` 对象的 `orders` 属性，这次您将得到一个非空列表，如下所示:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "source": [
    "c1.orders"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[<Order:1>, <Order:2>, <Order:3>]"
      ]
     },
     "metadata": {},
     "execution_count": 17
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "From the other side of the relationship, we can access the `Customer` object to which the order belongs to using the `customer` attribute on the `Order` object.\r\n",
    "\r\n",
    "从关系的另一端，我们可以使用 `Order` 对象上的 `Customer` 属性访问订单所属的 `Customer` 对象。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "source": [
    "o1.customer"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<Customer:1-tmiller>"
      ]
     },
     "metadata": {},
     "execution_count": 18
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "The customer c1 now has two orders. To view the order lines in an order use the `order_lines` attribute of the Order object.\r\n",
    "\r\n",
    "客户 c1 现在有两个订单。要按顺序查看订单行，请使用 `Order` 对象的 `order_lines` 属性。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "source": [
    "c1.orders[0].order_lines, c1.orders[1].order_lines"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "([<OrderLine:1>, <OrderLine:2>], [<OrderLine:3>, <OrderLine:4>])"
      ]
     },
     "metadata": {},
     "execution_count": 19
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "To access the item in an order line use item attribute.\r\n",
    "\r\n",
    "若要访问 订单行 中的物体，请使用 item 属性。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "source": [
    "for ol in c1.orders[0].order_lines:\r\n",
    "    ol.id, ol.item, ol.quantity\r\n",
    "    \r\n",
    "print('-------')\r\n",
    "    \r\n",
    "for ol in c1.orders[1].order_lines:\r\n",
    "    ol.id, ol.item, ol.quantity"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(1, <Item:1-Chair>, 3)"
      ]
     },
     "metadata": {},
     "execution_count": 20
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(2, <Item:2-Pen>, 2)"
      ]
     },
     "metadata": {},
     "execution_count": 20
    },
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "-------\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(3, <Item:1-Chair>, 1)"
      ]
     },
     "metadata": {},
     "execution_count": 20
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(4, <Item:2-Pen>, 4)"
      ]
     },
     "metadata": {},
     "execution_count": 20
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Remember that, all of this became possible because of the relationship() we defined in our models.\r\n",
    "\r\n",
    "请记住，正是由于我们在模型中定义了关系 `relationship()` ，这一切才能成为可能。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Querying Data 查询数据\r\n",
    "\r\n",
    "To query database we use the query() method of the session object. The query() method returns an object of type `sqlalchemy.orm.query.Query`, simply called Query. The Query object represents the SELECT statement that will be used to query the database. The following table lists some common methods of the Query class.\r\n",
    "\r\n",
    "要查询数据库，我们使用 `session` 对象的 query() 方法。Query() 方法返回 `sqlalchemy.orm.query.Query` 类型的对象, 以下简称为`Query`。 `Query` 对象表示用于查询数据库的 SELECT 语句。下表列出了 Query 类的一些常用方法。\r\n",
    "\r\n",
    "| Method                        | Description                                                  |\r\n",
    "| :---------------------------- | :----------------------------------------------------------- |\r\n",
    "| `all()`                       | returns the result of the query (represented by `Query`) as a list. 返回查询结果(由 )作为一个列表|\r\n",
    "| `count()`                     | returns the total number of records in the query. 返回查询中的记录总数           |\r\n",
    "| `first()`                     | returns the first result of the query or `None`, if there are no rows in the result. 返回查询的第一个结果,如果没有发现这样的物体则返回`None` |\r\n",
    "| `scalar()`                    | returns the first column of the first row or `None` if the result set is empty. If multiple rows are encountered it throws `MultipleResultsFound` exception. 返回第一行的第一列或者`None`--如果结果集是空的。如果遇到多行，则抛出 `MultipleResultsFound` 例外|\r\n",
    "| `one`                         | returns exactly only row. If it encounters multiple rows it throws `MultipleResultsFound` exception. If the result set is empty it throws `NoResultFound` exception. 只返回唯一行，如果遇到多行，则抛出`MultipleResultsFound`异常, 如果结果集为空，则抛出 `NoResultFound` 例外|\r\n",
    "| `get(pk)`                     | returns an object that matches the given primary key (pk), or `None`, if no such object is found. 返回与给定主键(pk)匹配的对象，如果没有发现这样的物体则返回`None` |\r\n",
    "| `filter(*criterion)`          | returns a new `Query` instance after applying the `WHERE` clause to the query. 返回一个新的实例，在应用 `WHERE`子句后|\r\n",
    "| `limit(limit)`                | return a new `Query` instance after applying the `LIMIT` clause to the query. 返回一个新的实例，在应用 `LIMIT`(有限数量)子句后|\r\n",
    "| `offset(offset)`              | return a new `Query` instance after applying the `OFFSET` clause to the query. 返回一个新的实例，在应用 `OFFSET` (偏移)子句后|\r\n",
    "| `order_by(*criterion)`        | return a new `Query` instance after applying `ORDER BY` clause to the query. 返回一个新的实例，在应用 `ORDER BY` 排序子句后|\r\n",
    "| `join(*props, **kwargs)`      | return a new `Query` instance after creating SQL INNER JOIN on the query. 返回一个新的实例，在查询上创建 内连接 之后 |\r\n",
    "| `outerjoin(*props, **kwargs)` | return a new `Query` instance after creating SQL LEFT OUTER JOIN on the query. 返回一个新的实例，在应用 左外连接 后|\r\n",
    "| `group_by(*criterion)`        | return a new `Query` instance after adding `GROUP BY` clause to the query. 返回一个新的实例，在应用 `GROUP BY` (分组统计)子句后|\r\n",
    "| `having(criterion)`           | return a new `Query` instance after adding `HAVING` clause to the query. 返回一个新的实例，在应用 `HAVING` (分组筛选) 子句后|"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### all() 方法\r\n",
    "In its simplest form, the query() method can take one or more model class or columns as arguments. The following code returns all the records from the customers table.\r\n",
    "\r\n",
    "在其最简单的形式中，query()方法可以接受一个或多个模型类或列作为参数。下面的代码返回来自 `customers` 表的所有记录。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "source": [
    "session.query(Customer).all()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[<Customer:1-tmiller>,\n",
       " <Customer:2-scottharvey>,\n",
       " <Customer:3-johnlara>,\n",
       " <Customer:4-sarahtomlin>,\n",
       " <Customer:5-tmiller>,\n",
       " <Customer:6-scottharvey>]"
      ]
     },
     "metadata": {},
     "execution_count": 21
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Similarly, the following code returns all the records from the `items` and `orders` table.\r\n",
    "\r\n",
    "类似地，下面的代码返回 `items` 和 `orders` 表中的所有记录。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "source": [
    "session.query(Item).all()\r\n",
    "session.query(Order).all()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[<Item:1-Chair>,\n",
       " <Item:2-Pen>,\n",
       " <Item:3-Headphone>,\n",
       " <Item:4-Travel Bag>,\n",
       " <Item:5-Keyboard>,\n",
       " <Item:6-Monitor>,\n",
       " <Item:7-Watch>,\n",
       " <Item:8-Water Bottle>]"
      ]
     },
     "metadata": {},
     "execution_count": 22
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[<Order:1>, <Order:2>, <Order:3>]"
      ]
     },
     "metadata": {},
     "execution_count": 22
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "To get the raw SQL used to query the database simply print the `sqlalchemy.orm.query.Query` object as follows:\r\n",
    "\r\n",
    "要获得用于查询数据库的原始 SQL，只需打印 `sqlalchemy.orm.query.Query` 对象，如下所示:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "source": [
    "print(session.query(Customer))"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT customers.id AS customers_id, customers.first_name AS customers_first_name, customers.last_name AS customers_last_name, customers.username AS customers_username, customers.email AS customers_email, customers.address AS customers_address, customers.town AS customers_town, customers.created_on AS customers_created_on, customers.updated_on AS customers_updated_on \n",
      "FROM customers\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Calling all() method on a large result set is inefficient instead we can use a for loop to iterate over the Query object as follows::\r\n",
    "\r\n",
    "在大型结果集上调用 all ()方法效率低下，我们可以使用 for 循环迭代 Query 对象，如下所示:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "source": [
    "q = session.query(Customer)\r\n",
    "\r\n",
    "for c in q:\r\n",
    "    print(c.id, c.first_name)"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "1 Toby\n",
      "2 Scott\n",
      "3 John\n",
      "4 Sarah\n",
      "5 Toby\n",
      "6 Scott\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "The preceding queries have returned data from all columns of the table. We can prevent this by passing the column names explicitly to the query() method as follows:\r\n",
    "\r\n",
    "前面的查询已从表的所有列返回数据。我们可以通过以下方式将 `列名` 显式传递给 `query()` 方法来防止这种情况:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "source": [
    "session.query(Customer.id, Customer.first_name).all()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(1, 'Toby'),\n",
       " (2, 'Scott'),\n",
       " (3, 'John'),\n",
       " (4, 'Sarah'),\n",
       " (5, 'Toby'),\n",
       " (6, 'Scott')]"
      ]
     },
     "metadata": {},
     "execution_count": 25
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### count() method 计数法\r\n",
    "\r\n",
    "The count() method returns the number of results returned by the query.\r\n",
    "\r\n",
    "Count ()方法返回查询返回的结果数。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "session.query(Customer).count() # get the total number of records in the customers table\r\n",
    "session.query(Item).count()  # get the total number of records in the items table\r\n",
    "session.query(Order).count()  # get the total number of records in the orders table"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### first() method 第一条\r\n",
    "\r\n",
    "The first() method returns the first result of the query or None if the query returns zero results.\r\n",
    "\r\n",
    "First ()方法返回查询的第一个结果，如果查询结果返回零，则返回 None。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "source": [
    "session.query(Customer).first()\r\n",
    "session.query(Item).first()\r\n",
    "session.query(Order).first()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<Customer:1-tmiller>"
      ]
     },
     "metadata": {},
     "execution_count": 28
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<Item:1-Chair>"
      ]
     },
     "metadata": {},
     "execution_count": 28
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<Order:1>"
      ]
     },
     "metadata": {},
     "execution_count": 28
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### get() method Get() 方法\r\n",
    "\r\n",
    "The get() method returns the instance which matches the primary key passed to it or None if no such object found.\r\n",
    "\r\n",
    "Get ()方法返回与传递给它的主键相匹配的实例，如果没有找到这样的对象，则返回 None。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "source": [
    "session.query(Customer).get(1)\r\n",
    "session.query(Item).get(1)\r\n",
    "session.query(Order).get(100)"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<Customer:1-tmiller>"
      ]
     },
     "metadata": {},
     "execution_count": 27
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<Item:1-Chair>"
      ]
     },
     "metadata": {},
     "execution_count": 27
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### filter() method  过滤()方法\r\n",
    "\r\n",
    "The filter() method allows us to filter the result by adding WHERE clause to the query. At the minimum, it accepts a column, an operator and a value. Here is an example:\r\n",
    "\r\n",
    "Filter()方法允许我们通过向查询中添加 WHERE 子句来过滤结果。它至少接受一个列、一个运算符和一个值。下面是一个例子:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "source": [
    "session.query(Customer).filter(Customer.first_name == 'John').all()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[<Customer:3-johnlara>]"
      ]
     },
     "metadata": {},
     "execution_count": 26
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "This query returns all the customers whose first name is John. The SQL equivalent of the query is:\r\n",
    "\r\n",
    "此查询返回 first name 名为 John 的所有客户. 查询的 SQL 等效项是:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "source": [
    "print(session.query(Customer).filter(Customer.first_name == 'John'))"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT customers.id AS customers_id, customers.first_name AS customers_first_name, customers.last_name AS customers_last_name, customers.username AS customers_username, customers.email AS customers_email, customers.address AS customers_address, customers.town AS customers_town, customers.created_on AS customers_created_on, customers.updated_on AS customers_updated_on \n",
      "FROM customers \n",
      "WHERE customers.first_name = %(first_name_1)s\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "```sql\r\n",
    "SELECT\r\n",
    "    customers.id AS customers_id,\r\n",
    "    customers.first_name AS customers_first_name,\r\n",
    "    customers.last_name AS customers_last_name,\r\n",
    "    customers.username AS customers_username,\r\n",
    "    customers.email AS customers_email,\r\n",
    "    customers.address AS customers_address,\r\n",
    "    customers.town AS customers_town,\r\n",
    "    customers.created_on AS customers_created_on,\r\n",
    "    customers.updated_on AS customers_updated_on\r\n",
    "FROM\r\n",
    "    customers\r\n",
    "WHERE\r\n",
    "    customers.first_name = %(first_name_1)s\r\n",
    "```    "
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "The string %(first_name_1)s in the WHERE clause is a placeholder and will be replaced by the actual value (i.e John) when the query is executed.\r\n",
    "\r\n",
    "WHERE 子句中的字符串 `%(first_name_1)s` 是一个占位符，在执行查询时将被实际值(即 John)替换。\r\n",
    "\r\n",
    "We can pass multiple filters to the filter() method and they will be joined together using SQL AND operator. For example:\r\n",
    "\r\n",
    "我们可以向 `filter()` 方法传递多个过滤器，并且它们将使用 `sql` 的 `and` 操作符连接在一起。例如:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "session.query(Customer).filter(Customer.id <= 5, Customer.town == \"Norfolk\").all()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "This query returns all the customers whose primary key is less than or equal to 5 and town name starts with Nor. Its SQL equivalent is:\r\n",
    "\r\n",
    "此查询返回主键小于或等于 `5` 并且 镇名以 `Nor` 开头的所有客户。它的 `SQL` 对等语句是:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "source": [
    "print(session.query(Customer).filter(Customer.id <= 5, Customer.town.like(\"Nor%\")))"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT customers.id AS customers_id, customers.first_name AS customers_first_name, customers.last_name AS customers_last_name, customers.username AS customers_username, customers.email AS customers_email, customers.address AS customers_address, customers.town AS customers_town, customers.created_on AS customers_created_on, customers.updated_on AS customers_updated_on \n",
      "FROM customers \n",
      "WHERE customers.id <= %(id_1)s AND customers.town LIKE %(town_1)s\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "```sql\r\n",
    "SELECT customers.id AS customers_id, \r\n",
    "customers.first_name AS customers_first_name, \r\n",
    "customers.last_name AS customers_last_name, \r\n",
    "customers.username AS customers_username, \r\n",
    "customers.email AS customers_email, \r\n",
    "customers.address AS customers_address, \r\n",
    "customers.town AS customers_town, \r\n",
    "customers.created_on AS customers_created_on, \r\n",
    "customers.updated_on AS customers_updated_on \r\n",
    "FROM customers \r\n",
    "WHERE customers.id <= %(id_1)s \r\n",
    "AND customers.town LIKE %(town_1)s\r\n",
    "```"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Another way to combine conditions is to use conjunctions (i.e. and_(), or_() and not_()). Here are some examples:\r\n",
    "\r\n",
    "组合条件的另一种方法是使用连词(例如 `and_()`, `or_()` and `not_()`). 以下例子:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "source": [
    "from sqlalchemy import or_, and_, not_\r\n",
    "\r\n",
    "# find all customers who either live in Peterbrugh or Norfolk\r\n",
    "\r\n",
    "session.query(Customer).filter(or_(\r\n",
    "    Customer.town == 'Peterbrugh', \r\n",
    "    Customer.town == 'Norfolk'\r\n",
    ")).all()\r\n",
    "\r\n",
    "\r\n",
    "# find all customers whose first name is John and live in Norfolk\r\n",
    "\r\n",
    "session.query(Customer).filter(and_(\r\n",
    "    Customer.first_name == 'John', \r\n",
    "    Customer.town == 'Norfolk'\r\n",
    ")).all()\r\n",
    "\r\n",
    "\r\n",
    "# find all johns who don't live in Peterbrugh\r\n",
    "\r\n",
    "session.query(Customer).filter(and_(\r\n",
    "    Customer.first_name == 'John', \r\n",
    "    not_(\r\n",
    "        Customer.town == 'Peterbrugh', \r\n",
    "    )\r\n",
    ")).all()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[<Customer:3-johnlara>, <Customer:4-sarahtomlin>]"
      ]
     },
     "metadata": {},
     "execution_count": 31
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[<Customer:3-johnlara>]"
      ]
     },
     "metadata": {},
     "execution_count": 31
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[<Customer:3-johnlara>]"
      ]
     },
     "metadata": {},
     "execution_count": 31
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### IS NULL 为空"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "session.query(Order).filter(Order.date_shipped == None).all()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### IS NOT NULL 非空"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "session.query(Order).filter(Order.date_shipped != None).all()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### IN"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "session.query(Customer).filter(Customer.first_name.in_(['Toby', 'Sarah'])).all()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### NOT IN"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "session.query(Customer).filter(Customer.first_name.notin_(['Toby', 'Sarah'])).all()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### BETWEEN"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "session.query(Item).filter(Item.cost_price.between(10, 50)).all()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### NOT BETWEEN"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "session.query(Item).filter(not_(Item.cost_price.between(10, 50))).all()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### LIKE"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "session.query(Item).filter(Item.name.like(\"%r\")).all()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "session.query(Item).filter(Item.name.ilike(\"w%\")).all()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### NOT LIKE"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "session.query(Item).filter(not_(Item.name.like(\"W%\"))).all()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## limit() method 有限方法\r\n",
    "\r\n",
    "The limit() method adds LIMIT clause to the query. It accepts the number of rows you want to return from the query.\r\n",
    "\r\n",
    "LIMIT ()方法向查询添加 LIMIT 子句。它接受您希望从查询返回的行数。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "session.query(Customer).limit(2).all()\r\n",
    "session.query(Customer).filter(Customer.address.ilike(\"%avenue\")).limit(2).all()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "print(session.query(Customer).limit(2))\r\n",
    "print(session.query(Customer).filter(Customer.address.ilike(\"%avenue\")).limit(2))"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### offset() method 偏移\r\n",
    "\r\n",
    "The offset() method adds the OFFSET clause to the query. It accepts offset as an argument. It is commonly used with the limit() clause.\r\n",
    "\r\n",
    "OFFSET ()方法将 OFFSET 子句添加到查询中。它接受偏移量作为参数。它通常与 limit()子句一起使用。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "source": [
    "session.query(Customer).limit(2).offset(2).all()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[<Customer:3-johnlara>, <Customer:4-sarahtomlin>]"
      ]
     },
     "metadata": {},
     "execution_count": 33
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "The SQL equivalent of the above query is as follows:\r\n",
    "\r\n",
    "上述查询的 SQL 等价物如下:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "source": [
    "print(session.query(Customer).limit(2).offset(2))"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT customers.id AS customers_id, customers.first_name AS customers_first_name, customers.last_name AS customers_last_name, customers.username AS customers_username, customers.email AS customers_email, customers.address AS customers_address, customers.town AS customers_town, customers.created_on AS customers_created_on, customers.updated_on AS customers_updated_on \n",
      "FROM customers \n",
      " LIMIT %(param_1)s, %(param_2)s\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### order_by() method 排序\r\n",
    "\r\n",
    "The order_by() method is used to order the result by adding ORDER BY clause to the query. It accepts column names on which the order should be based. By default, it sorts in ascending order.\r\n",
    "\r\n",
    "ORDER _ BY ()方法通过向查询中添加 orderby 子句来对结果进行排序。它接受订单所基于的列名。默认情况下，它按升序排序。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "session.query(Item).filter(Item.name.ilike(\"wa%\")).all()\r\n",
    "session.query(Item).filter(Item.name.ilike(\"wa%\")).order_by(Item.cost_price).all()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "To sort in descending order use desc() function as follows:\r\n",
    "\r\n",
    "使用 desc() 函数按降序排序如下:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "from sqlalchemy import desc\r\n",
    "session.query(Item).filter(Item.name.ilike(\"wa%\")).order_by(desc(Item.cost_price)).all()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### join() method 连接\r\n",
    "\r\n",
    "The join() method is used to create SQL INNER JOIN. It accepts table name for which you want to create SQL JOIN.\r\n",
    "\r\n",
    "JOIN() 方法用于创建 sqlinner JOIN。它接受要为其创建 sql join 的表名。\r\n",
    "\r\n",
    "Let's use join() method to find all the customers who have one or more orders.\r\n",
    "\r\n",
    "让我们使用 join() 方法来查找所有有一个或多个订单的客户。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "session.query(Customer).join(Order).all()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "This query is equivalent to the following SQL:\r\n",
    "\r\n",
    "此查询相当于以下 SQL:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "print(session.query(Customer).join(Order))"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "The join() method is commonly used to get the data from one or more table in a single query. For example:\r\n",
    "\r\n",
    "Join () 方法通常用于在单个查询中从一个或多个表获取数据。例如:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "source": [
    "session.query(Customer.id, Customer.username, Order.id).join(Order).all()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(1, 'tmiller', 1), (1, 'tmiller', 2), (1, 'tmiller', 3)]"
      ]
     },
     "metadata": {},
     "execution_count": 34
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "We can create SQL JOIN for more than two table by chaining join() method as follows:\r\n",
    "\r\n",
    "通过链接 JOIN ()方法，我们可以为两个以上的表创建 SQL JOIN:\r\n",
    "\r\n",
    "``` session.query(Table1).join(Table2).join(Table3).join(Table4).all() ```\r\n",
    "\r\n",
    "Here is another example, which uses 3 joins to find all the items in the John Green's first order.\r\n",
    "\r\n",
    "下面是另一个示例，它使用3个联接来查找 John Green 的第一个订单中的所有项。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "session.query(\r\n",
    "    Customer.first_name, \r\n",
    "    Item.name, \r\n",
    "    Item.selling_price, \r\n",
    "    OrderLine.quantity\r\n",
    ").join(Order).join(OrderLine).join(Item).filter(\r\n",
    "    Customer.first_name == 'John',\r\n",
    "    Customer.last_name == 'Green',\r\n",
    "    Order.id == 1,\r\n",
    ").all()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### outerjoin() method  外连接()方法\r\n",
    "\r\n",
    "The outerjoin() method works just like join() but creates LEFT OUTER JOIN.\r\n",
    "\r\n",
    "Outerjoin() 方法的工作方式与 JOIN ()类似，但是创建了 LEFT OUTER JOIN。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "session.query(        \r\n",
    "    Customer.first_name,\r\n",
    "    Order.id,\r\n",
    ").outerjoin(Order).all()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "In this query, the left table is the customers table. Thus, it will return all the rows from customers table (the left table), and only the rows that meet the join condition are returned from the orders table (the right table).\r\n",
    "\r\n",
    "在这个查询中，左边的表是 customers 表。因此，它将返回来自 customers 表(左表)的所有行，并且只有满足连接条件的行从 orders 表(右表)返回。\r\n",
    "\r\n",
    "You can also create a FULL OUTER JOIN by passing full=True to outerjoin() method. For example:\r\n",
    "\r\n",
    "还可以通过传递 FULL = True to outerjoin ()方法创建 FULL OUTER JOIN:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "session.query(        \r\n",
    "    Customer.first_name,\r\n",
    "    Order.id,\r\n",
    ").outerjoin(Order, full=True).all()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### group_by() method 分组比()法\r\n",
    "\r\n",
    "We group results using the group_by() method. It accepts one or more columns and groups the rows according to the values in the column.\r\n",
    "\r\n",
    "我们使用 group _ by ()方法对结果进行分组。它接受一个或多个列，并根据列中的值对行进行分组。\r\n",
    "\r\n",
    "The following query uses join() and group_by() to count the number of orders made by john green.\r\n",
    "\r\n",
    "下面的查询使用 join ()和 group _ by ()来计算 john green 生成的订单数量。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "from sqlalchemy import func\r\n",
    "\r\n",
    "session.query(func.count(Customer.id)).join(Order).filter(\r\n",
    "    Customer.first_name == 'John',\r\n",
    "    Customer.last_name == 'Green',    \r\n",
    ").group_by(Customer.id).scalar()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### having() method  有()方法\r\n",
    "To filter out the results based on the values returned by aggregate functions we use having() method which adds the HAVING clause to the SELECT statement. Just like the where() clause, it accepts a condition.\r\n",
    "\r\n",
    "为了根据聚合函数返回的值过滤掉结果，我们使用 HAVING ()方法，该方法将 HAVING 子句添加到 SELECT 语句中。就像 where ()子句一样，它接受一个条件。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "# find the number of customers lives in each town\r\n",
    "\r\n",
    "session.query(\r\n",
    "    func.count(\"*\").label('town_count'),    \r\n",
    "    Customer.town\r\n",
    ").group_by(Customer.town).having(func.count(\"*\") > 2).all()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Dealing with Duplicates 去重\r\n",
    "\r\n",
    "To deal with the duplicate rows in the result set we use the DISTINCT option. We can add DISTINCT option to the SELECT statement using the distinct() method. For example:\r\n",
    "\r\n",
    "为了处理结果集中的重复行，我们使用 DISTINCT 选项。我们可以使用 DISTINCT ()方法向 SELECT 语句添加 DISTINCT 选项。例如:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "from sqlalchemy import distinct\r\n",
    "\r\n",
    "session.query(Customer.town).filter(Customer.id  < 10).all()\r\n",
    "session.query(Customer.town).filter(Customer.id  < 10).distinct().all()\r\n",
    "\r\n",
    "session.query(        \r\n",
    "    func.count(distinct(Customer.town)),\r\n",
    "    func.count(Customer.town)\r\n",
    ").all()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Casting  类型申明\r\n",
    "\r\n",
    "Casting (converting) data from one type to another is a common operation and is done via cast() function from the sqlalchemy package.\r\n",
    "\r\n",
    "将数据从一种类型转换为另一种类型是一种常见的操作，通过 sqlalchemy 包中的 cast ()函数完成。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "from sqlalchemy import cast, Date, union\r\n",
    "\r\n",
    "session.query(\r\n",
    "    cast(func.pi(), Integer),\r\n",
    "    cast(func.pi(), Numeric(10,2)),\r\n",
    "    cast(\"2010-12-01\", DateTime),\r\n",
    "    cast(\"2010-12-01\", Date),\r\n",
    ").all()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Unions 合并查询\r\n",
    "\r\n",
    "To union queries we use the union() method of the Query object. It takes one or more queries. For example:\r\n",
    "\r\n",
    "为了合并查询，我们使用 Query 对象的 union ()方法。它接受一个或多个查询。例如:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "s1 = session.query(Item.id, Item.name).filter(Item.name.like(\"Wa%\"))\r\n",
    "s2 = session.query(Item.id, Item.name).filter(Item.name.like(\"%e%\"))\r\n",
    "s1.union(s2).all()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "By default, union() removes all the duplicate rows from the result set. If you want to keep the duplicates use union_all().\r\n",
    "\r\n",
    "默认情况下，union ()从结果集中删除所有重复的行。如果要保留副本，请使用 union_all ()。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "s1.union_all(s2).all()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Updating Data 更新数据\r\n",
    "\r\n",
    "To update an object simply set its attribute to a new value, add the object to the session and commit the changes.\r\n",
    "\r\n",
    "要更新对象，只需将其属性设置为新值，将对象添加到会话并提交更改。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "i = session.query(Item).get(8)\r\n",
    "i.selling_price = 25.91\r\n",
    "session.add(i)\r\n",
    "session.commit()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "This way we can only update a single object at a time. To update multiple rows at once use update() method of the Query object. It returns the total number of records updated. For example:\r\n",
    "\r\n",
    "这样我们一次只能更新一个对象。使用 Query 对象的 update ()方法一次更新多个行。它返回更新的记录总数。例如:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "session.query(Item).filter(\r\n",
    "    Item.name.ilike(\"W%\")\r\n",
    ").update({\"quantity\": 60}, synchronize_session='fetch')\r\n",
    "session.commit()"
   ],
   "outputs": [],
   "metadata": {
    "scrolled": true
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Deleting Data 删除数据\r\n",
    "\r\n",
    "To delete an object use the delete() method of the session object. It accepts an object and marks it to be deleted in the next commit.\r\n",
    "\r\n",
    "要删除对象，请使用会话对象的 delete ()方法。它接受一个对象，并将其标记为在下次提交时删除。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "i = session.query(Item).filter(Item.name == 'Monitor').one()\r\n",
    "i\r\n",
    "session.delete(i)\r\n",
    "session.commit()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "This commit removes the Monitor from the items table.\r\n",
    "\r\n",
    "此提交将从 items 表中删除 Monitor。\r\n",
    "\r\n",
    "To delete multiple records at once use the delete() method of the Query object.\r\n",
    "\r\n",
    "使用 Query 对象的 delete ()方法一次删除多条记录。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "session.query(Item).filter(\r\n",
    "    Item.name.ilike(\"W%\")\r\n",
    ").delete(synchronize_session='fetch')\r\n",
    "session.commit()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Raw Queries  原始查询语句\r\n",
    "\r\n",
    "ORM also give you the flexibility to directly to use directly use SQL using the text() function. For example:\r\n",
    "\r\n",
    "ORM 还提供了使用 text ()函数直接使用 SQL 的灵活性。例如:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "from sqlalchemy import text\r\n",
    "\r\n",
    "session.query(Customer).filter(text(\"first_name = 'John'\")).all()\r\n",
    "\r\n",
    "session.query(Customer).filter(text(\"town like 'Nor%'\")).all()\r\n",
    "\r\n",
    "session.query(Customer).filter(text(\"town like 'Nor%'\")).order_by(text(\"first_name, id desc\")).all()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Transactions 事务\r\n",
    "\r\n",
    "A transaction is a way to execute a set of SQL statements such that either all of the statements are executed successfully or nothing at all. If any of the statement involved in the transaction fails then the database is returned to the state it was in before the transaction was initiated.\r\n",
    "\r\n",
    "事务是一种执行一组 SQL 语句的方法，这样可以成功地执行所有语句，也可以什么都不执行。如果事务中涉及的任何语句失败，那么数据库将返回到事务启动之前的状态。\r\n",
    "\r\n",
    "We currently have two orders in the database. The process of dispatching the order is as follows:\r\n",
    "\r\n",
    "我们目前在数据库中有两个订单。发送订单的过程如下:\r\n",
    "\r\n",
    "Set the shipping date in the 中设置发货日期date_shipped column in 列入orders table 表\r\n",
    "\r\n",
    "Subtract the quantity of ordered items from the 方法中减去已订购项的数量items table 表\r\n",
    "\r\n",
    "Both of these actions must be performed as a unit to ensure that the data in the tables are correct.\r\n",
    "\r\n",
    "这两个操作必须作为一个单元来执行，以确保表中的数据是正确的。\r\n",
    "\r\n",
    "In the following listing, we define dispatch_order() method which accepts order_id as an argument, and performs the above-mentioned tasks in a transaction.\r\n",
    "\r\n",
    "在下面的清单中，我们定义 dispatch _ order ()方法，该方法接受 order _ id 作为参数，并在事务中执行上述任务。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "from sqlalchemy import update\r\n",
    "from sqlalchemy.exc import IntegrityError\r\n",
    "from datetime import datetime\r\n",
    "\r\n",
    "\r\n",
    "def dispatch_order(order_id):\r\n",
    "\r\n",
    "    # check whether order_id is valid or not\r\n",
    "    order = session.query(Order).get(order_id)\r\n",
    "    \r\n",
    "    if not order:\r\n",
    "        raise ValueError(\"Invalid order id: {}.\".format(order_id))    \r\n",
    "        \r\n",
    "    if order.date_shipped:\r\n",
    "        print(\"Order already shipped.\")\r\n",
    "        return\r\n",
    "\r\n",
    "    try:\r\n",
    "        for i in order.order_lines:\r\n",
    "            i.item.quantity = i.item.quantity - i.quantity            \r\n",
    "        \r\n",
    "        order.date_shipped = datetime.now()                            \r\n",
    "        session.commit()\r\n",
    "        print(\"Transaction completed.\")\r\n",
    "\r\n",
    "    except IntegrityError as e:\r\n",
    "        print(e)\r\n",
    "        print(\"Rolling back ...\")\r\n",
    "        session.rollback()\r\n",
    "        print(\"Transaction failed.\")"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Our first order is for 3 chairs and 2 pens. Calling dispatch_order() function with order id of 1, will return the following output:\r\n",
    "\r\n",
    "我们的第一笔订单是3把椅子和2支钢笔。调用 dispatch _ order ()函数，其顺序 id 为1，将返回以下输出:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "dispatch_order(1)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "At this point, items and order_lines tables should look like this:\r\n",
    "\r\n",
    "现在，items 和 order _ lines 表看起来应该是这样的:\r\n",
    "\r\n",
    "[]\r\n",
    "\r\n",
    "[]\r\n",
    "\r\n",
    "Our next order is for 1 chair and 4 pens, but we now only have 3 pens in the stock!\r\n",
    "\r\n",
    "我们的下一个订单是1把椅子和4支钢笔，但我们现在只有3支钢笔存货！\r\n",
    "\r\n",
    "Let's try running dispatch_order() for second order and see what happens.\r\n",
    "\r\n",
    "让我们尝试为二阶运行 dispatch _ order () ，看看会发生什么。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "dispatch_order(2)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "As expected, our shipment failed because we don't have enough pens in the stock and because we are using transaction our database is returned to the state it was in before the transaction was started.\r\n",
    "\r\n",
    "正如预期的那样，我们的发货失败了，因为我们没有足够的钢笔在库存，因为我们正在使用事务，我们的数据库返回到它在事务开始之前的状态。\r\n",
    "\r\n",
    "I hope this tutorial would be informative for you. If you have encountered any typo or error. Get in touch using the contact page or comment box below.\r\n",
    "\r\n",
    "我希望这个教程能为你提供信息。如果您遇到任何打印错误或错误。请使用下面的联系页面或评论框与我们联系。\r\n"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "[粗翻]  2021-08-24 Fully jupyter notebook and Chinese Verion CRAFTED BY YULK "
   ],
   "metadata": {}
  }
 ],
 "metadata": {
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3.9.6 64-bit"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  },
  "interpreter": {
   "hash": "c644d696b95f5e0f4df3c6556741cf30bcf9ea6ca93c3e1f29fcf31d885534fc"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}